<?php
	class ModelTestTest extends Model {
		public function jointest($data){
			if((int)$data['star'] == 0){
				$sqlstar = " AND c.star>0";
			}else if ((int)$data['star'] ==5){
				$sqlstar = " AND c.star=5";
			}
			$sql = "SELECT k.kname,k.kdetail,(SELECT COUNT(*) FROM complate WHERE kid = '".(int)$data['kid']."'" .$sqlstar . ") AS total FROM knowledge k LEFT JOIN complate c ON k.id = c.kid WHERE k.user_id = '" . $this->db->escape($data['user_id']) . "' GROUP BY k.kname,k.kdetail ORDER BY (SELECT COUNT(*) FROM complate WHERE kid = '".(int)$data['kid']."'". $sqlstar .")";
			$query = $this->db->query($sql);
			return $query->rows;
			// COUNT(c.id) AS isporc
		}
		public function kandlabeltest($label){
			$sql = "SELECT DISTINCT k.*, (SELECT COUNT(*) FROM complate WHERE kid = k.id AND star >'0') AS isend , (SELECT COUNT(*) FROM complate WHERE kid = k.id AND star ='5') AS ispass FROM knowledge k INNER JOIN label l ON l.kid = k.id WHERE l.label LIKE '%". $this->db->escape($label) . "%'";

			$query =  $this->db->query($sql);
			return $query->rows;
		}
	}